package cn.net.autocode.platform.table.ddl.sqlserver;

import cn.hutool.core.util.StrUtil;
import cn.net.autocode.platform.table.ddl.DataBaseDDL;
import com.alibaba.fastjson2.JSONObject;
import cn.net.autocode.dbManager.Dao;


public class SqlServerDataBaseDDL implements DataBaseDDL {

    private final Dao dao;

    public SqlServerDataBaseDDL(Dao dao){
        this.dao = dao;
    }
   
    public void createTable(String tableName) {
        String sql = "create table "+tableName+
                " (id nvarchar(36) not null primary key," +
                "sort_no int default 0," +
                "remark nvarchar(255))";
        dao.execute(sql);
    }

    public void createServTable(String tableName) {
        String sql = "create table "+tableName+
                " (id nvarchar(36) not null primary key)";
        dao.execute(sql);
    }

   
    public void addColumn(JSONObject column, String tableName) {
        String type = column.getString("type").toUpperCase();
        column.getString("name");
        String sql = "alter table "+tableName+" add "+column.getString("name") + " ";

        sql = switch (type) {
            case "CHAR" -> sql + "nchar(" + column.getIntValue("length") + ") ";
            case "VARCHAR" -> sql + "nvarchar(" + column.getIntValue("length") + ") ";
            case "TEXT", "INT", "DATETIME" -> sql + type;
            case "TIMESTAMP" -> sql + "timestamp(" + column.getIntValue("length") + ") ";
            case "DECIMAL" -> sql + "numeric(" + column.getIntValue("length") + "," + column.getIntValue("scale") + ") ";
            case "LONGTEXT" -> sql + "TEXT";
            case "LONGBLOB" -> sql + "IMAGE";
            default -> throw new RuntimeException("未定义的类型："+type);
        };

        //设置默认值
        String defaultvalue = column.getString("defaultvalue");
        if(StrUtil.isNotBlank(defaultvalue)&&!defaultvalue.startsWith("@")){
            sql = switch (type) {
                case "CHAR", "VARCHAR", "LONGTEXT" -> sql + " default '" + defaultvalue + "'";
                case "TIMESTAMP", "DECIMAL", "INT", "DATETIME" -> sql + " default " + defaultvalue;
                default -> sql;
            };

        }

        //设置非空约束
        String isnullable = column.getString("isnullable");
        if(StrUtil.isBlank(defaultvalue)&&"N".equals(isnullable)){
            sql = sql + " not null";
        }

        //设置字段说明,sql server不支持通过sql设置注释
       /* String remarks = column.getString("remarks");
        if(StrUtil.isNotBlank(remarks)){
            sql = sql +" comment '"+remarks+"'";
        }*/
        dao.execute(sql);
    }

   
    public void modifyColumn(JSONObject column, String tableName) {
        String type = column.getString("type");
        String sql = "alter table "+tableName+" alter column "+column.getString("name") + " ";
        sql = switch (type) {
            case "CHAR" -> sql+"nchar("+column.getIntValue("length") +")";
            case "VARCHAR" -> sql+"nvarchar("+column.getIntValue("length") +")";
            case "timestamp", "TIMESTAMP" -> sql + "timestamp(" + column.getIntValue("length") + ") ";
            case "DECIMAL" -> sql + "DECIMAL(" + column.getIntValue("length") + "," + column.getIntValue("scale") + ") ";
            case "INT" -> sql+"int";
            case "TEXT", "LONGTEXT" -> sql+"ntext";
            case "DATETIME" -> sql+"datetime";
            case "LONGBLOB" -> sql + "image";
            default -> sql + type;
        };

        //设置长度


        String isnullable = column.getString("isnullable");
        String defaultvalue = column.getString("defaultvalue");
        if((StrUtil.isBlank(defaultvalue)||defaultvalue.startsWith("@"))&&"N".equals(isnullable)){
            sql = sql + " not null";
        }

        dao.execute(sql);

        //删除默认约束
        String selectDefault = """
                    SELECT dc.name conname
                              FROM sys.tables t
                              INNER JOIN sys.columns c ON t.object_id = c.object_id
                              LEFT OUTER JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id AND c.column_id = dc.parent_column_id
                              WHERE t.name = ?  and c.name=?
                              ORDER BY c.column_id;
                    """;
        String ccdefault = dao.queryValue(selectDefault,String.class,tableName,column.getString("name"));
        if(StrUtil.isNotBlank(ccdefault)){
            String dropDefault = "alter table "+tableName+" drop constraint "+ccdefault;
            dao.execute(dropDefault);
        }
        
        //设置默认值及非空约束
        if(StrUtil.isNotBlank(defaultvalue)&&!defaultvalue.startsWith("@")){
            //查询存在的约束
           
            String defaultSql = "alter table "+tableName+" add constraint df_"+tableName+"_"+column.getString("name")+" default ";
            defaultSql = switch (type) {
                case "CHAR", "VARCHAR", "LONGTEXT" -> defaultSql + " '" + defaultvalue + "'";
                case "timestamp", "DECIMAL", "INT", "DATETIME" -> defaultSql + " " + defaultvalue;
                default -> defaultSql + " '" + defaultvalue + "'";
            };
            defaultSql = defaultSql+" for "+column.getString("name");
            dao.execute(defaultSql);
        }

    /*    //设置字段说明
        String remarks = column.getString("remarks");
        if(StrUtil.isNotBlank(remarks)){
            sql = sql +" comment '"+remarks+"'";
        }
        dao.execute(sql);*/

    }

   
    public void delColumn(String column, String tableName) {
        if(this.checkColumnExists(tableName,column)){
            String sql = "ALTER TABLE "+tableName+" DROP column "+column;
            dao.execute(sql);
        }
    }

    public boolean checkColumnExists(String tableName,String columnName){
        JSONObject info;
        //获取object_id
        String sql0 = "select so.id object_id,CAST(so.name as nvarchar(255)) as TABLE_NAME, cast(sep.value as nvarchar(500)) as REMARKS " +
                " from sysobjects so left JOIN sys.extended_properties sep on sep.major_id = so.id and sep.minor_id = 0 where (xtype = 'U' or xtype = 'v') " +
                " and  so.name=?";
        info = dao.queryJSON(sql0,tableName);

        Integer objectId = info.getIntValue("objectId");
        //--获取列的信息、默认值、备注
        String sql1 = "select a.name,b.name from sys.tables a,sys.columns b where a.object_id=b.object_id and a.object_id=? and b.name=?";
        JSONObject columns = dao.queryJSON(sql1,objectId,columnName);

        return !columns.isEmpty();
    }

}
